Create Staging Table in Staging Database and Populate the Staging Tables - Continued 11
CREATE FUNCTION dbo.ufxCountSuspectRace ( @J VARCHAR(6), @SuspectRace VARCHAR(250) ) RETURNS INT AS BEGIN DECLARE @NoOfSuspectsInvolved INT, @i INT, @Count INT DECLARE @lOffRaceActual VARCHAR(250) SET @NoOfSuspectsInvolved = 0 SET @i = LEN(@SuspectRace) SET @Count = 0 WHILE @NoOfSuspectsInvolved < @i BEGIN SET @NoOfSuspectsInvolved = @NoOfSuspectsInvolved + 1 IF SUBSTRING(@SuspectRace,@NoOfSuspectsInvolved,LEN(@J)) = @J BEGIN SET @Count = @Count + 1 END END RETURN @Count; END
SQL command Code
SELECT [IncidentNumber],[IncidentType],[IncidentDate],
convert(date, [IncidentDate]) as [Date],
convert(varchar(8), convert(time,[IncidentDate] )) as [Time],
Year([IncidentDate]) as [Year],
Month([IncidentDate]) as [Month],
DateName( month , DateAdd( month , (Month([IncidentDate])) , -1 ) ) as [MonthName],
[IncidentLocation],[City],[State],[Zip],
[ReportingDistrict] ReportingDistrictID,b.[NAME] ReportingDistrictName,
[GeoLocation],[HandlingUnitID],[HandlingUnitName],[NumberOfInvolvedDeputies],[DeputyRace],
[NumberOfSuspects],[SuspectRace],
[dbo].[ufxCountSuspectRace]('W',[SuspectRace] ) NumberOfWhiteSuspects,
[dbo].[ufxCountSuspectRace]('B',[SuspectRace] ) NumberOfBlackSuspects,
[dbo].[ufxCountSuspectRace]('H',[SuspectRace] ) NumberOfHispanicSuspects,
[dbo].[ufxCountSuspectRace]('UNKNOWN',[SuspectRace] ) NumberOfUnknownSuspects,
[NumberOfSuspectWounded],[NumberOfSuspectDeceased],
[WeaponInvolvedCategory],[WeaponInvolvedCategoryDesc],
[Latitude],[Longitude],[FileDate]
FROM [dbo].[IncidentsExtract] a
LEFT JOIN [dbo].[ReportingDistrict] b
ON a.ReportingDistrict=b.[RD NUM]